﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace RCA1._0
{
	public class CONGTYDAO: DataProvider
	{
		public DataTable LayBang()
		{
			OleDbConnection cn = this.Connect();
			string strSQL = "select * from CONGTY where TinhTrangCongTy=1 ";
			OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
			DataTable dt = new DataTable();
			da.Fill(dt);
			cn.Close();
			return dt;
		}

        #region "Lấy đối tượng"
        private CONGTYDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();
 
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            CONGTYDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO= new CONGTYDTO();
                //tempDTO.MaDiaChiTruSoChinh = Convert.ToInt32(dr["MaDiaChiTruSoChinh"].ToString());
                
                tempDTO.Email = dr["Email"].ToString();
                tempDTO.IDCongTy = Convert.ToInt32(dr["IDCongTy"]);
                tempDTO.MaLoaiCongTy = Convert.ToInt32(dr["MaLoaiCongTy"]);
                tempDTO.NganhNgheKinhDoanhChinh = dr["NganhNgheKinhDoanhChinh"].ToString();
                tempDTO.NgayNhapLieu = DateTime.Parse(dr["NgayNhapLieu"].ToString());
                tempDTO.NgayChinhSua = DateTime.Parse(dr["NgayChinhSua"].ToString());
                tempDTO.TenCongTy = dr["TenCongTy"].ToString();
                tempDTO.TenGiaoDichCongTy = dr["TenGiaoDichCongTy"].ToString();
                tempDTO.TenVietTat = dr["TenVietTat"].ToString();
                tempDTO.MenhGiaCoPhan = Convert.ToDouble(dr["MenhGiaCoPhan"]);
                tempDTO.VonDieuLeBangChu = dr["VonDieuLeBangChu"].ToString();
                tempDTO.VonDieuLeSo = Convert.ToDouble(dr["VonDieuLeSo"]);
                tempDTO.Website = dr["Website"].ToString();
                tempDTO.TinhTrangCongTy = Convert.ToInt32(dr["TinhTrangCongTy"]);
                tempDTO.TongSoCoPhan = Convert.ToDouble(dr["TongSoCoPhan"].ToString());
                tempDTO.SoCoPhanDangKyMua = Convert.ToDouble(dr["SoCoPhanDangKyMua"].ToString());
                tempDTO.SoCoPhanChaoBan = Convert.ToDouble(dr["SoCoPhanChaoBan"].ToString());
                tempDTO.VonPhapDinh = Convert.ToDouble(dr["VonPhapDinh"].ToString());
                tempDTO.DiaChiNhanThongBaoThue = dr["DiaChiNhanThongBaoThue"].ToString();
                tempDTO.NgayBatDauHoatDong = DateTime.Parse(dr["NgayBatDauHoatDong"].ToString());
                tempDTO.NamTaiChinhBatDau = DateTime.Parse(dr["NamTaiChinhBatDau"].ToString());
                tempDTO.NamTaiChinhKetThuc = DateTime.Parse(dr["NamTaiChinhKetThuc"].ToString());
                tempDTO.TongSoLaoDong = dr["TongSoLaoDong"].ToString();
                tempDTO.DangKyXuatKhau = dr["DangKyXuatKhau"].ToString();
                tempDTO.TaiKhoanNganHang = dr["TaiKhoanNganHang"].ToString();
                tempDTO.TaiKhoanKhoBac = dr["TaiKhoanKhoBac"].ToString();
                tempDTO.MaGiamDocCongTy = Convert.ToInt32(dr["MaGiamDocCongTy"]);
                tempDTO.MaKeToanTruong = Convert.ToInt32(dr["MaKeToanTruong"]);
            }
            cn.Close(); 
            return tempDTO;
        }

        #endregion


        #region "Lấy danh sách"

        private List<CONGTYDTO> LayDSach(String sql)
		{
			OleDbConnection cn = this.Connect();
			OleDbCommand cmd = new OleDbCommand(sql, cn);
			OleDbDataReader dr = cmd.ExecuteReader();
			CONGTYDTO tempDTO;
            List<CONGTYDTO> dsCONGTY = new List<CONGTYDTO>();
			while (dr.Read())
			{
                tempDTO = new CONGTYDTO();
				tempDTO.Email = dr["Email"].ToString();
				tempDTO.IDCongTy = Convert.ToInt32(dr["IDCongTy"]);
                tempDTO.MaLoaiCongTy = Convert.ToInt32(dr["MaLoaiCongTy"]);
				tempDTO.NgayNhapLieu = DateTime.Parse(dr["NgayNhapLieu"].ToString());                               
                tempDTO.NgayChinhSua = DateTime.Parse(dr["NgayChinhSua"].ToString());                
				tempDTO.TenCongTy = dr["TenCongTy"].ToString();
				tempDTO.TenGiaoDichCongTy = dr["TenGiaoDichCongTy"].ToString();
				tempDTO.TenVietTat = dr["TenVietTat"].ToString();
				tempDTO.MenhGiaCoPhan = Convert.ToDouble(dr["MenhGiaCoPhan"]);
				tempDTO.VonDieuLeBangChu = dr["VonDieuLeBangChu"].ToString();
				tempDTO.VonDieuLeSo = Convert.ToDouble(dr["VonDieuLeSo"]);
				tempDTO.Website = dr["Website"].ToString();
                tempDTO.TinhTrangCongTy = Convert.ToInt32(dr["TinhTrangCongTy"]);
                tempDTO.TongSoCoPhan = Convert.ToDouble(dr["TongSoCoPhan"].ToString());
                tempDTO.SoCoPhanDangKyMua = Convert.ToDouble(dr["SoCoPhanDangKyMua"].ToString());
                tempDTO.SoCoPhanChaoBan = Convert.ToDouble(dr["SoCoPhanChaoBan"].ToString());
                tempDTO.VonPhapDinh = Convert.ToDouble(dr["VonPhapDinh"].ToString());
                tempDTO.DiaChiNhanThongBaoThue = dr["DiaChiNhanThongBaoThue"].ToString();
                tempDTO.NgayBatDauHoatDong = DateTime.Parse(dr["NgayBatDauHoatDong"].ToString());
                tempDTO.NamTaiChinhBatDau = DateTime.Parse(dr["NamTaiChinhBatDau"].ToString());
                tempDTO.NamTaiChinhKetThuc = DateTime.Parse(dr["NamTaiChinhKetThuc"].ToString());
                tempDTO.TongSoLaoDong = dr["TongSoLaoDong"].ToString();
                tempDTO.DangKyXuatKhau = dr["DangKyXuatKhau"].ToString();
                tempDTO.TaiKhoanNganHang = dr["TaiKhoanNganHang"].ToString();
                tempDTO.TaiKhoanKhoBac = dr["TaiKhoanKhoBac"].ToString();
				dsCONGTY.Add(tempDTO);
			}
			return dsCONGTY;
		}

        public List<CONGTYDTO> LayDanhSach()
        {
            String sql = "Select * From CONGTY Where TinhTrangCongTy=1 ";
            return LayDSach(sql);
        }

        #endregion


        #region "Lấy danh sách tên"

        private List<CONGTYDTO> LayDSachTen(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            CONGTYDTO tempDTO;
            List<CONGTYDTO> dsCONGTY = new List<CONGTYDTO>();
            while (dr.Read())
            {
                tempDTO = new CONGTYDTO();
                //tempDTO.IDCongTy = Convert.ToInt32(dr["IDCongTy"]);
                tempDTO.TenCongTy = dr["TenCongTy"].ToString();
                dsCONGTY.Add(tempDTO);
            }
            return dsCONGTY;
        }

        public List<CONGTYDTO> LayDanhSachTen()
        {
            String sql = "Select * From CONGTY Where TinhTrangCongTy=1";
            return LayDSachTen(sql);
        }

        #endregion


        #region "Tìm kiếm"
        
        public List<CONGTYDTO> TimKiemTheoTenCongTy(String tenCongTy)
        {
            String sql = "Select * From CONGTY Where TenCongTy like '%" + tenCongTy + "%' and TinhTrangCongTy=1 ";
            return LayDSach(sql);
        }

        public List<CONGTYDTO> TimKiemTheoTenVietTat(String tenVietTat)
        {
            String sql = "Select * From CONGTY Where TenVietTat like '%" + tenVietTat + "%' and TinhTrangCongTy=1 ";
            return LayDSach(sql);
        }

        public CONGTYDTO TimTheoMa(int MaCongTy)
        {
            string strSQL = "select * from CONGTY Where TinhTrangCongTy=1 and IDCongTy=" + MaCongTy.ToString();
		    return LayDoiTuong(strSQL);
		}

        #endregion


        #region "Thêm"

        public void Them(CONGTYDTO tempDTO)
		{
			OleDbConnection cn = this.Connect();
            string strSQL = "insert into CONGTY( Email, MaLoaiCongTy, NganhNgheKinhDoanhChinh, NgayNhapLieu, NgayChinhSua, TenCongTy, TenGiaoDichCongTy, TenVietTat, MenhGiaCoPhan, VonDieuLeBangChu, VonDieuLeSo, Website, TinhTrangCongTy, SoCoPhanDangKyMua, SoCoPhanChaoBan, VonPhapDinh, MaDiaChiNhanThongBaoThue, NgayBatDauHoatDong, NamTaiChinhBatDau, NamTaiChinhKetThuc, TongSoLaoDong, DangKyXuatKhau, TaiKhoanNganHang, TaiKhoanKhoBac, MaGiamDocCongTy, MaKeToanTruong) values (@MaDiaChiTruSoChinh, @Email, @MaLoaiCongTy, @NganhNgheKinhDoanhChinh, @NgayNhapLieu, @NgayChinhSua, @TenCongTy, @TenGiaoDichCongTy, @TenVietTat, @MenhGiaCoPhan, @VonDieuLeBangChu, @VonDieuLeSo, @Website, @TinhTrangCongTy, @SoCoPhanDangKyMua, @SoCoPhanChaoBan, @VonPhapDinh, @MaDiaChiNhanThongBaoThue, @NgayBatDauHoatDong, @NamTaiChinhBatDau, @NamTaiChinhKetThuc, @TongSoLaoDong, @DangKyXuatKhau, @TaiKhoanNganHang, @TaiKhoanKhoBac, @MaGiamDocCongTy, @MaKeToanTruong)";
			OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            
			//cmd.Parameters.Add("@DienThoai", OleDbType.LongVarWChar).Value = tempDTO.DienThoai;
			cmd.Parameters.Add("@Email", OleDbType.LongVarWChar).Value = tempDTO.Email;
			//cmd.Parameters.Add("@Fax", OleDbType.LongVarWChar).Value = tempDTO.Fax;
            cmd.Parameters.Add("@MaLoaiCongTy", OleDbType.Integer).Value = tempDTO.MaLoaiCongTy;
			cmd.Parameters.Add("@NganhNgheKinhDoanhChinh", OleDbType.LongVarWChar).Value = tempDTO.NganhNgheKinhDoanhChinh;
			cmd.Parameters.Add("@NgayNhapLieu", OleDbType.DBDate).Value = tempDTO.NgayNhapLieu;
            cmd.Parameters.Add("@NgayChinhSua", OleDbType.DBDate).Value = tempDTO.NgayNhapLieu;
			cmd.Parameters.Add("@TenCongTy", OleDbType.LongVarWChar).Value = tempDTO.TenCongTy;
			cmd.Parameters.Add("@TenGiaoDichCongTy", OleDbType.LongVarWChar).Value = tempDTO.TenGiaoDichCongTy;
			cmd.Parameters.Add("@TenVietTat", OleDbType.LongVarWChar).Value = tempDTO.TenVietTat;
			cmd.Parameters.Add("@MenhGiaCoPhan", OleDbType.Double).Value = tempDTO.MenhGiaCoPhan;
			cmd.Parameters.Add("@VonDieuLeBangChu", OleDbType.LongVarWChar).Value = tempDTO.VonDieuLeBangChu;
			cmd.Parameters.Add("@VonDieuLeSo", OleDbType.Double).Value = tempDTO.VonDieuLeSo;
			cmd.Parameters.Add("@Website", OleDbType.LongVarWChar).Value = tempDTO.Website;
            cmd.Parameters.Add("@TinhTrangCongTy", OleDbType.LongVarWChar).Value = tempDTO.TinhTrangCongTy=1;
            
            cmd.Parameters.Add("@SoCoPhanDangKyMua", OleDbType.Double).Value = tempDTO.SoCoPhanDangKyMua;
            cmd.Parameters.Add("@SoCoPhanChaoBan", OleDbType.Double).Value = tempDTO.SoCoPhanChaoBan;
            cmd.Parameters.Add("@VonPhapDinh", OleDbType.Double).Value = tempDTO.VonPhapDinh;
            cmd.Parameters.Add("@DiaChiNhanThongBaoThue", OleDbType.Integer).Value = tempDTO.DiaChiNhanThongBaoThue;
            cmd.Parameters.Add("@NgayBatDauHoatDong", OleDbType.DBDate).Value = tempDTO.NgayBatDauHoatDong;
            cmd.Parameters.Add("@NamTaiChinhBatDau", OleDbType.DBDate).Value = tempDTO.NamTaiChinhBatDau;
            cmd.Parameters.Add("@NamTaiChinhKetThuc", OleDbType.DBDate).Value = tempDTO.NamTaiChinhKetThuc;
            cmd.Parameters.Add("@TongSoLaoDong", OleDbType.LongVarWChar).Value = tempDTO.TongSoLaoDong;
            cmd.Parameters.Add("@DangKyXuatKhau", OleDbType.LongVarWChar).Value = tempDTO.DangKyXuatKhau;
            cmd.Parameters.Add("@TaiKhoanNganHang", OleDbType.LongVarWChar).Value = tempDTO.TaiKhoanNganHang;
            cmd.Parameters.Add("@TaiKhoanKhoBac", OleDbType.LongVarWChar).Value = tempDTO.TaiKhoanKhoBac;       
            cmd.Parameters.Add("@MaGiamDocCongTy", OleDbType.Integer).Value = tempDTO.MaGiamDocCongTy;
            cmd.Parameters.Add("@MaKeToanTruong", OleDbType.Integer).Value = tempDTO.MaKeToanTruong;
           
			cmd.ExecuteNonQuery();
			cn.Close();
		}

        #endregion


        #region "Xóa"

        public void Xoa(CONGTYDTO tempDTO)
		{
            tempDTO.TinhTrangCongTy = 2;
            Sua(tempDTO);
		}

        #endregion


        #region "Sửa"

        public void Sua(CONGTYDTO tempDTO)
		{
			OleDbConnection cn = this.Connect();
            string strSQL = "update CONGTY set MaDiaChiTruSoChinh = @MaDiaChiTruSoChinh, Email = @Email, MaLoaiCongTy = @MaLoaiCongTy, NganhNgheKinhDoanhChinh = @NganhNgheKinhDoanhChinh, NgayChinhSua = @NgayChinhSua, TenCongTy = @TenCongTy, TenGiaoDichCongTy = @TenGiaoDichCongTy, TenVietTat = @TenVietTat, MenhGiaCoPhan = @MenhGiaCoPhan, VonDieuLeBangChu = @VonDieuLeBangChu, VonDieuLeSo = @VonDieuLeSo, Website = @Website, TinhTrangCongTy = @TinhTrangCongTy, SoCoPhanDangKyMua = @SoCoPhanDangKyMua, SoCoPhanChaoBan = @SoCoPhanChaoBan, VonPhapDinh = @VonPhapDinh, MaDiaChiNhanThongBaoThue = @MaDiaChiNhanThongBaoThue, NgayBatDauHoatDong = @NgayBatDauHoatDong, NamTaiChinhBatDau = @NamTaiChinhBatDau, NamTaiChinhKetThuc = @NamTaiChinhKetThuc, TongSoLaoDong = @TongSoLaoDong, DangKyXuatKhau = @DangKyXuatKhau, TaiKhoanNganHang = @TaiKhoanNganHang, TaiKhoanKhoBac = @TaiKhoanKhoBac, MaGiamDocCongTy = @MaGiamDocCongTy, MaKeToanTruong = @MaKeToanTruong where IDCongTy = @IDCongTy";
			OleDbCommand cmd = new OleDbCommand(strSQL, cn);
			
			cmd.Parameters.Add("@Email", OleDbType.LongVarWChar).Value = tempDTO.Email;
            cmd.Parameters.Add("@MaLoaiCongTy", OleDbType.Integer).Value = tempDTO.MaLoaiCongTy;
			cmd.Parameters.Add("@NganhNgheKinhDoanhChinh", OleDbType.LongVarWChar).Value = tempDTO.NganhNgheKinhDoanhChinh;
            cmd.Parameters.Add("@NgayChinhSua", OleDbType.DBDate).Value = tempDTO.NgayChinhSua;
			cmd.Parameters.Add("@TenCongTy", OleDbType.LongVarWChar).Value = tempDTO.TenCongTy;
			cmd.Parameters.Add("@TenGiaoDichCongTy", OleDbType.LongVarWChar).Value = tempDTO.TenGiaoDichCongTy;
			cmd.Parameters.Add("@TenVietTat", OleDbType.LongVarWChar).Value = tempDTO.TenVietTat;
            cmd.Parameters.Add("@MenhGiaCoPhan", OleDbType.Double).Value = tempDTO.MenhGiaCoPhan;
			cmd.Parameters.Add("@VonDieuLeBangChu", OleDbType.LongVarWChar).Value = tempDTO.VonDieuLeBangChu;
            cmd.Parameters.Add("@VonDieuLeSo", OleDbType.Double).Value = tempDTO.VonDieuLeSo;
			cmd.Parameters.Add("@Website", OleDbType.LongVarWChar).Value = tempDTO.Website;
            cmd.Parameters.Add("@TinhTrangCongTy", OleDbType.LongVarWChar).Value = tempDTO.TinhTrangCongTy;
			cmd.Parameters.Add("@IDCongTy", OleDbType.Integer).Value = tempDTO.IDCongTy;

            cmd.Parameters.Add("@SoCoPhanDangKyMua", OleDbType.Double).Value = tempDTO.SoCoPhanDangKyMua;
            cmd.Parameters.Add("@SoCoPhanChaoBan", OleDbType.Double).Value = tempDTO.SoCoPhanChaoBan;
            cmd.Parameters.Add("@VonPhapDinh", OleDbType.Double).Value = tempDTO.VonPhapDinh;
            cmd.Parameters.Add("@DiaChiNhanThongBaoThue", OleDbType.Integer).Value = tempDTO.DiaChiNhanThongBaoThue;
            cmd.Parameters.Add("@NgayBatDauHoatDong", OleDbType.DBDate).Value = tempDTO.NgayBatDauHoatDong;
            cmd.Parameters.Add("@NamTaiChinhBatDau", OleDbType.DBDate).Value = tempDTO.NamTaiChinhBatDau;
            cmd.Parameters.Add("@NamTaiChinhKetThuc", OleDbType.DBDate).Value = tempDTO.NamTaiChinhKetThuc;
            cmd.Parameters.Add("@TongSoLaoDong", OleDbType.LongVarWChar).Value = tempDTO.TongSoLaoDong;
            cmd.Parameters.Add("@DangKyXuatKhau", OleDbType.LongVarWChar).Value = tempDTO.DangKyXuatKhau;
            cmd.Parameters.Add("@TaiKhoanNganHang", OleDbType.LongVarWChar).Value = tempDTO.TaiKhoanNganHang;
            cmd.Parameters.Add("@TaiKhoanKhoBac", OleDbType.LongVarWChar).Value = tempDTO.TaiKhoanKhoBac;
            cmd.Parameters.Add("@MaGiamDocCongTy", OleDbType.Integer).Value = tempDTO.MaGiamDocCongTy;
            cmd.Parameters.Add("@MaKeToanTruong", OleDbType.Integer).Value = tempDTO.MaKeToanTruong;
			cmd.ExecuteNonQuery();
			cn.Close();
        }

        #endregion

    }
}